package com.iisigroup.adrclient.dao.impl;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.swing.JTextArea;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.iisigroup.adrclient.common.DateUtils;
import com.iisigroup.adrclient.common.GenericDao;
import com.iisigroup.adrclient.common.StringUtils;
import com.iisigroup.adrclient.dao.AdrClientDao;
import com.iisigroup.adrclient.vo.Adr200VO;
import com.iisigroup.adrclient.vo.AdrAreaVO;
import com.iisigroup.adrclient.vo.AdrPostVO;
import com.iisigroup.adrclient.vo.AdrSprdVO;
import com.iisigroup.adrclient.vo.AdrStrBlockOrderVO;
import com.iisigroup.adrclient.vo.AdrStrBlockVO;
import com.iisigroup.adrclient.vo.Hud250VO;
import com.iisigroup.adrclient.vo.ResultVO;
import com.iisigroup.adrclient.vo.WordMapVO;

@Repository
public class AdrClientDaoImpl extends GenericDao implements AdrClientDao {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	private static final Integer BUFFER_SIZE = 32768;
	private static final String ENCODING = "MS950";
	private static final Integer BATCH_SIZE = 10000;
	private static final String OUTPUT_PATH = new File("").getAbsolutePath().toString() + "/output/";
	private static final String ERROR_PATH = new File("").getAbsolutePath().toString() + "/error/";
	
	private enum Sql {
		PROCEDURE_ADR_220("{call sp_get220 (?, ?)}"),
		COUNT_ADR_220("select count(*) from [adr_str_block_vil_220] where [year] = ? and county_code = ?"),
		PROCEDURE_ADR_260("{call sp_get260 (?, ?)}"),
		COUNT_ADR_260("select count(*) from (select distinct top 100 percent county_code,"
						+ "isnull(neig, '') as neig, isnull(hh_code, '') as hh_code , isnull(county_name, '') as county_name,"
						+ "isnull(town_name, '') as town_name, isnull(vil, '') as vil, isnull([address], '') as [address],"
						+ "isnull(area_code, '') as area_code, isnull([str], '') as [str], isnull(sec, '') as sec, [weight],"
						+ "isnull(no1, '') as no1, isnull(no2, '') as no2, isnull(no3, '') as no3, isnull(no4, '') as no4, isnull(no5, '') as no5, "
						+ "isnull(main_floor, '') as main_floor, isnull(sub_floor, '') as sub_floor, isnull(sp_char3, '') as sp_char3, "
						+ "isnull(edit_date, '') as edit_date, isnull(edit_type, '') as edit_type from [adr_380] where [year] = ?"
						+ "and county_code = ? and err_code is null) as a"),
		PROCEDURE_ADR_270("{call sp_get270 (?, ?)}"),
		COUNT_ADR_270("select count(*) from [adr_380] where isnull(ltrim(rtrim(id)), '') != '' and [year] = ? and county_code = ? and err_code is null go"),
		PROCEDURE_ADR_POST("{call sp_getPOST (?, ?)}"),
		COUNT_ADR_POST("select count(*) from (select distinct isnull([year], '') as [year],"
						+ "isnull(county_name, '') as county_name, isnull(town_name, '') as town_name,"
						+ "isnull([str], '') as [str], isnull(sec, '') as sec, isnull(lan, '') as lan,"
						+ "isnull(aly, '') as aly, isnull(sec_c, '') as sec_c, isnull(lan_c, '') as lan_c, "
						+ "isnull(aly_c, '') as aly_c, substring(isnull(area_code, ''), 1, 4) as code from [adr_380]"
						+ "where [year] = ? and county_code = ? and err_code is null) as a"),
		PROCEDURE_ADR_SPRD("{call sp_getSPRD (?)}"),
		COUNT_ADR_SPRD("select count(*) from (select distinct top 100 percent [year], county_code, substring(area_code, 3, 2) as town_code, county_name, town_name, [str] from [adr_380] "
						+ "where substring([str], len([str]), 1) not like '[路街]' and [year] = ?) as a"),
		COUNT_ADR_200("select count(*) from adr_200 where [year] = ? and county_code = ?"),
		DELETE_ADR_200("delete from adr_200 where [year] = ? and county_code = ?"),
		INSERT_ADR_200("INSERT INTO ADR_200(RESERVATION, [YEAR], COUNTY_CODE, HH_CODE, VIL, NEIG, [ADDRESS], EDIT_DATE, EDIT_TYPE)"
						+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"),
		COUNT_HUD_250("select count(*) from hud_250 where [year] = ? and county_code = ?"),
		DELETE_HUD_250("delete from hud_250 where [year] = ? and county_code = ?"),
		INSERT_HUD_250("INSERT INTO HUD_250(SOURCE_NO, [YEAR], COUNTY_CODE, ID, NAME, BIRTH, MARRIAGE, REL_MAST, "
						+ "MAST_ID, HUD_ID, ABORIGINE, REL_MAST_CODE, HH_CODE, MIX1, AREA_CODE, COUNTY_NAME, TOWN_NAME, VIL, NEIG, [ADDRESS], MIX2, MIX3, MOVE_IN)"
						+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"),
		SELECT_CTY_TAB("select city_code, city_name from adr_cty_tab"),
		PROCEDURE_AREA("{call sp_getVil (?)}"),
		PROCEDURE_CITY("{call sp_getCity (?)}"),
		CALCULATE_ADR_380_WEIGHT("update adr_380 set [weight] = dbo.calWeight(lan, aly, sub_aly, no1), [weight2] = dbo.calWeightOE(lan, aly, sub_aly, no1) where [year] = ? and county_code = ?"),
		INSERT_ADR_POST("insert into adr_post([year], county_code, area_town, county_name, town_name, str, sec, sec_c, lan, lan_c, aly, aly_c, adr_aly) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"),
		DELETE_ADR_POST("delete adr_post where [year] = ? and county_code = ?"),
		INSERT_ADR_AREA("insert into adr_area(county_code, county_name, town_code, town_name, vil_code, vil_name) values(?, ?, ?, ?, ?, ?)"),
		DELETE_ADR_AREA("delete adr_area where county_code = ?"),
		DISABLE_ADR_200("ALTER TABLE adr_200 NOCHECK CONSTRAINT ALL;ALTER INDEX ALL ON adr_200 DISABLE;"),
		DISABLE_HUD_250("ALTER TABLE hud_250 NOCHECK CONSTRAINT ALL;ALTER INDEX ALL ON hud_250 DISABLE;"),
		DISABLE_ADR_380("ALTER TABLE adr_380 NOCHECK CONSTRAINT ALL;ALTER INDEX ALL ON adr_380 DISABLE;"),
		DISABLE_ADR_POST("ALTER TABLE adr_post NOCHECK CONSTRAINT ALL;ALTER INDEX ALL ON adr_post DISABLE;"),
		DISABLE_ADR_AREA("ALTER TABLE adr_area NOCHECK CONSTRAINT ALL;ALTER INDEX ALL ON adr_area DISABLE;"),
		ENABLE_ADR_200("ALTER TABLE ADR_200 CHECK CONSTRAINT ALL;ALTER INDEX ALL ON ADR_200 REBUILD;"),
		ENABLE_HUD_250("ALTER TABLE HUD_250 CHECK CONSTRAINT ALL;ALTER INDEX ALL ON HUD_250 REBUILD;"),
		ENABLE_ADR_380("ALTER TABLE ADR_380 CHECK CONSTRAINT ALL;ALTER INDEX ALL ON ADR_380 REBUILD;"),
		ENABLE_ADR_POST("ALTER TABLE ADR_POST CHECK CONSTRAINT ALL;ALTER INDEX ALL ON ADR_POST REBUILD;"),
		ENABLE_ADR_AREA("ALTER TABLE ADR_AREA CHECK CONSTRAINT ALL;ALTER INDEX ALL ON ADR_AREA REBUILD;"),
		FIX_ADR_200("update ADR_200 set neig = right('000' + dbo.trim(neig), 3) where len(neig) < 3"),
		FIX_HUD_250("update HUD_250 set area_code = substring(area_code, 1, 7) + right('000' + substring(area_code, 8, len(area_code) - 7), 3) where len(area_code) < 10; update HUD_250 set neig = right('000' + dbo.trim(neig), 3) where len(neig) < 3"),
		INSERT_ADR_SPRD("insert into adr_str_tab_spec(county_code, county_name, town_code, town_name, str) values(?, ?, ?, ?, ?)"),
		DELETE_ADR_SPRD("delete adr_str_tab_spec where county_code = ?"),
		SELECT_WORD_MAP("select wrong_word, right_word from adr_word_map where county_code = ? order by len(wrong_word) desc"),
		DELETE_ADR_380("delete adr_380 where [year] = ? and county_code = ?"),
		PROCEDURE_INIT_ADR_220("{call sp_init220 (?, ?)}"),
		PROCEDURE_CREATE_ADR_220("{call sp_create220 (?, ?)}"),
		PROCEDURE_ORDER_150("{call sp_order150 (?, ?)}"),
		PROCEDURE_ORDER_168("{call sp_order168 (?, ?)}"),
		DELETE_150("delete adr_str_block_150 where [year] = ? and county_code = ?"),
		DELETE_168("delete adr_str_block_vil_168 where [year] = ? and county_code = ?"),
		INSERT_150("insert into adr_str_block_150 ([year], county_code, area_code, census_code, county_name, town_name, vil, [str], sec, sec_c, lan, lan_c, aly, aly_c, bgn_no, end_no, odd_even, block_no) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, dbo.z_convert(?, 0), dbo.z_convert(?, 0), ?, ?)"),
		INSERT_168("insert into adr_str_block_vil_168 ([year], county_code, area_code, census_code, county_name, town_name, vil, [str], sec, sec_c, lan, lan_c, aly, aly_c, sub_aly, sub_aly_c, bgn_no, end_no, odd_even, block_no) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, dbo.z_convert(?, 0), dbo.z_convert(?, 0), ?, ?)"),
		PROCEDURE_ADR_380("{call sp_get380 (?, ?)}"),
		PROCEDURE_ADR_150("{call sp_get150 (?, ?)}"),
		PROCEDURE_ADR_168("{call sp_get168 (?, ?)}"),
		PRODECURE_WORD_MAP("{call sp_getWordMap}"),
		INSERT_WORD_MAP("insert into adr_word_map (county_code, county_name, wrong_word, right_word) values (?, ?, ?, ?)"),
		TRUNCATE_WORD_MAP("truncate table adr_word_map");
		
		private final String value;
		Sql(final String value) {
			this.value = value;
		}
		@Override
		public String toString() {
			return value;
		}
	}
	
	private enum Column {
		COLUMN_SERIAL("serial"),
		COLUMN_YEAR("year"),
		COLUMN_CONTENT("content"),
		COLUMN_CTIY_CODE("city_code"),
		COLUMN_CTIY_NAME("city_name");
		private final String value;
		Column(final String value) {
			this.value = value;
		}
		@Override
		public String toString() {
			return value;
		}
	}
	
	@Override
	public Boolean uploadAdr200(String year, String countyCode, String url, final JTextArea console) {
		console.append("開始上傳門牌資料...\n");
		File file = new File(url);
		File error = new File(ERROR_PATH + "門牌/" + year + "." + DateUtils.getCurrentDate() + ".ERROR.L200.txt");
		BufferedReader input = null;
		BufferedWriter output = null;
		error.getParentFile().mkdirs();
		List<Adr200VO> list = new ArrayList<Adr200VO>();
		Map<String, String> map = this.getWordMap(countyCode);
		try {
			input = new BufferedReader(new InputStreamReader(new FileInputStream(file), ENCODING), BUFFER_SIZE);
			output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(error), ENCODING), BUFFER_SIZE);
			Integer serial = 0;
			Integer count = 0;
			
			jdbcTemplate.update(Sql.DELETE_ADR_200.toString(), year, countyCode);
			jdbcTemplate.update(Sql.DISABLE_ADR_200.toString());
			console.append("正在輸入到資料庫...\n");
			for (String line = null; (line = input.readLine()) != null; ) {
				serial++;
				byte[] bytes = line.getBytes(ENCODING);
				if (bytes.length == 139) {
					String reservation = new String(Arrays.copyOfRange(bytes, 0, 60), ENCODING);
//					String countyCode = new String(Arrays.copyOfRange(bytes, 63, 65), ENCODING);
					String hhCode = new String(Arrays.copyOfRange(bytes, 60, 68), ENCODING);
					String vil = new String(Arrays.copyOfRange(bytes, 68, 76), ENCODING);
					String neig = new String(Arrays.copyOfRange(bytes, 76, 79), ENCODING);
					String address = new String(Arrays.copyOfRange(bytes, 81, 131), ENCODING);
					String editDate = new String(Arrays.copyOfRange(bytes, 131, 138), ENCODING);
					String editType = new String(Arrays.copyOfRange(bytes, 138, 139), ENCODING);
					
					Adr200VO vo = new Adr200VO();
					vo.setReservation(reservation);
					vo.setYear(year);
					vo.setCountyCode(countyCode);
					vo.setHhCode(hhCode);
					vil = StringUtils.removeSpace(vil);
					vil = this.replaceWord(vil, map);
					vo.setVil(vil);
					vo.setNeig(neig);
					address = StringUtils.removeSpace(address);
					address = this.replaceWord(address, map);
					vo.setAddress(address);
					vo.setEditDate(editDate);
					vo.setEditType(editType);
					
					list.add(vo);
					
					if (list.size() == BATCH_SIZE) {
						this.batch200(list);
						count += list.size();
						console.append("已輸入" + count + "筆資料到資料庫...\n");
						list.clear();
					}
					
				} else {
					output.write("第" + serial +"筆資料長度: " + bytes.length);
					output.newLine();
					output.write(line);
					output.newLine();
				}
			}
			
			if (list.size() > 0) {
				this.batch200(list);
				count += list.size();
				console.append("已輸入" + count + "筆資料到資料庫...\n");
			}
			jdbcTemplate.update(Sql.FIX_ADR_200.toString());
			jdbcTemplate.update(Sql.ENABLE_ADR_200.toString());
		} catch (Exception e) {
			return this.logError(e);
		} finally {
			try {
				if (output != null) {
					output.close();
				}
				if (input != null) {
					input.close();
				}
			} catch (IOException e) {
				return this.logError(e);
			}
		}
		return true;
	}

	@Override
	public Integer countAdr200(String year, String countyCode) {
		return jdbcTemplate.queryForObject(Sql.COUNT_ADR_200.toString(), Integer.class, year, countyCode);
	}

	@Override
	public Boolean uploadHud250(String year, String countyCode, String url, final JTextArea console) {
		console.append("開始上傳戶籍資料...\n");
		File file = new File(url);
		File error = new File(ERROR_PATH + "戶籍/" + year + "." + DateUtils.getCurrentDate() + ".ERROR.L250.txt");
		
		BufferedReader input = null;
		BufferedWriter output = null;
		error.getParentFile().mkdirs();
		List<Hud250VO> list = new ArrayList<Hud250VO>();
		Map<String, String> map = this.getWordMap(countyCode);
		try {
			input = new BufferedReader(new InputStreamReader(new FileInputStream(file), ENCODING), BUFFER_SIZE);
			output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(error), ENCODING), BUFFER_SIZE);
			Integer serial = 0;
			Integer count = 0;
			
			jdbcTemplate.update(Sql.DISABLE_HUD_250.toString());
			jdbcTemplate.update(Sql.DELETE_HUD_250.toString(), year, countyCode);
			console.append("正在輸入到資料庫...\n");
			for (String line = null; (line = input.readLine()) != null; ) {
				serial++;
				byte[] bytes = line.getBytes(ENCODING);
				if (bytes.length == 250) {
					String sourceNo = new String(Arrays.copyOfRange(bytes, 240, 250), ENCODING);
//					String countyCode = new String(Arrays.copyOfRange(bytes, 240, 242), ENCODING);
					String id = new String(Arrays.copyOfRange(bytes, 0, 10), ENCODING);
					String name = new String(Arrays.copyOfRange(bytes, 10, 34), ENCODING);
					String birth = new String(Arrays.copyOfRange(bytes, 34, 41), ENCODING);
					String marriage = new String(Arrays.copyOfRange(bytes, 41, 42), ENCODING);
					String relMast = new String(Arrays.copyOfRange(bytes, 42, 50), ENCODING);
					String mastId = new String(Arrays.copyOfRange(bytes, 50, 60), ENCODING);
					String hudId = new String(Arrays.copyOfRange(bytes, 60, 68), ENCODING);
					String aborigine = new String(Arrays.copyOfRange(bytes, 68, 69), ENCODING);
					String relMastCode = new String(Arrays.copyOfRange(bytes, 76, 78), ENCODING);
					String hhCode = new String(Arrays.copyOfRange(bytes, 78, 86), ENCODING);
					String mix1 = new String(Arrays.copyOfRange(bytes, 87, 97), ENCODING);
					String areaCode = new String(Arrays.copyOfRange(bytes, 172, 182), ENCODING);
					String countyName = new String(Arrays.copyOfRange(bytes, 97, 103), ENCODING);
					String townName = new String(Arrays.copyOfRange(bytes, 103, 111), ENCODING);
					String vil = new String(Arrays.copyOfRange(bytes, 113, 121), ENCODING);
					String neig = new String(Arrays.copyOfRange(bytes, 69, 72), ENCODING);
					String address = new String(Arrays.copyOfRange(bytes, 121, 171), ENCODING);
					String mix2 = new String(Arrays.copyOfRange(bytes, 189, 199), ENCODING);
					String mix3 = new String(Arrays.copyOfRange(bytes, 199, 240), ENCODING);
					String moveIn = new String(Arrays.copyOfRange(bytes, 182, 189), ENCODING);
					
					Hud250VO vo = new Hud250VO();
					vo.setSourceNo(sourceNo);
					vo.setYear(year);
					vo.setCountyCode(countyCode);
					vo.setId(id);
					vo.setName(name);
					vo.setBirth(birth);
					vo.setMarriage(marriage);
					vo.setRelMast(relMast);
					vo.setMastId(mastId);
					vo.setHudId(hudId);
					vo.setAborigine(aborigine);
					vo.setRelMastCode(relMastCode);
					vo.setHhCode(hhCode);
					vo.setMix1(mix1);
					vo.setAreaCode(areaCode);
					vo.setCountyName(StringUtils.removeSpace(countyName));
					townName = StringUtils.removeSpace(townName);
					townName = this.replaceWord(townName, map);
					vo.setTownName(townName);
					vil = StringUtils.removeSpace(vil);
					vil = this.replaceWord(vil, map);
					vo.setVil(vil);
					vo.setNeig(neig);
					address = StringUtils.removeSpace(address);
					address = this.replaceWord(address, map);
					vo.setAddress(address);
					vo.setMix2(mix2);
					vo.setMix3(mix3);
					vo.setMoveIn(moveIn);
					
					list.add(vo);
					
					if (list.size() == BATCH_SIZE) {
						this.batch250(list);
						count += list.size();
						console.append("已輸入" + count + "筆資料到資料庫...\n");
						list.clear();
					}
					
				} else {
					output.write("第" + serial +"筆資料長度: " + bytes.length);
					output.newLine();
					output.write(line);
					output.newLine();
				}
			}
			
			if (list.size() > 0) {
				count += list.size();
				console.append("已輸入" + count + "筆資料到資料庫...\n");
				this.batch250(list);
			}
			jdbcTemplate.update(Sql.FIX_HUD_250.toString());
			jdbcTemplate.update(Sql.ENABLE_HUD_250.toString());
		} catch (Exception e) {
			return this.logError(e);
		} finally {
			try {
				if (output != null) {
					output.close();
				}
				if (input != null) {
					input.close();
				}
			} catch (IOException e) {
				return this.logError(e);
			}
		}
		return true;
	}
	
	@Override
	public Integer countHud250(String year, String countyCode) {
		return jdbcTemplate.queryForObject(Sql.COUNT_HUD_250.toString(), Integer.class, year, countyCode);
	}

	@Override
	public List<ResultVO> getAdr220(final String year, final String countyCode) {
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_220.toString());
				cs.setString(1, year);
				cs.setString(2,countyCode);
				return cs;
			}
		};
		CallableStatementCallback<List<ResultVO>> callback = new CallableStatementCallback<List<ResultVO>>() {
			@Override
			public List<ResultVO> doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				List<ResultVO> list = new ArrayList<ResultVO>();
				while (rs.next()) {
					ResultVO vo = new ResultVO();
					vo.setSerial(rs.getInt(Column.COLUMN_SERIAL.toString()));
					vo.setYear(rs.getString(Column.COLUMN_YEAR.toString()));
					vo.setContent(rs.getNString(Column.COLUMN_CONTENT.toString()));
					list.add(vo);
				}
				return list;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Boolean outputAdr220(final String year, final String countyCode, final JTextArea console) {
		console.append("開始產製工商街廓資料...\n");
		final File file = new File(OUTPUT_PATH + "工商街廓/" + year + "." + countyCode + "." + DateUtils.getCurrentDate() + ".L253.txt");
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_220.toString());
				cs.setString(1, year);
				cs.setString(2, countyCode);
				return cs;
			}
		};
		CallableStatementCallback<Boolean> callback = new CallableStatementCallback<Boolean>() {
			@Override
			public Boolean doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				Boolean result = outputResult(rs, file, console);
				return result;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Integer countAdr220(String year, String countyCode) {
		return jdbcTemplate.queryForObject(Sql.COUNT_ADR_220.toString(), Integer.class, year, countyCode);
	}

	@Override
	public List<ResultVO> getAdr260(final String year, final String countyCode) {
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_260.toString());
				cs.setString(1, year);
				cs.setString(2,countyCode);
				return cs;
			}
		};
		CallableStatementCallback<List<ResultVO>> callback = new CallableStatementCallback<List<ResultVO>>() {
			@Override
			public List<ResultVO> doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				List<ResultVO> list = new ArrayList<ResultVO>();
				while (rs.next()) {
					ResultVO vo = new ResultVO();
					vo.setSerial(rs.getInt(Column.COLUMN_SERIAL.toString()));
					vo.setYear(rs.getString(Column.COLUMN_YEAR.toString()));
					vo.setContent(rs.getNString(Column.COLUMN_CONTENT.toString()));
					list.add(vo);
				}
				return list;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Boolean outputAdr260(final String year, final String countyCode, final JTextArea console) {
		console.append("開始產製地址明細資料...\n");
		final File file = new File(OUTPUT_PATH + "地址明細/" + year + "." + countyCode + "." + DateUtils.getCurrentDate() + ".L260.txt");
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_260.toString());
				cs.setString(1, year);
				cs.setString(2, countyCode);
				return cs;
			}
		};
		CallableStatementCallback<Boolean> callback = new CallableStatementCallback<Boolean>() {
			@Override
			public Boolean doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				Boolean result = outputResult(rs, file, console);
				return result;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}

	@Override
	public Integer countAdr260(String year, String countyCode) {
		return jdbcTemplate.queryForObject(Sql.COUNT_ADR_260.toString(), Integer.class, year, countyCode);
	}

	@Override
	public List<ResultVO> getAdr270(final String year, final String countyCode) {
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_270.toString());
				cs.setString(1, year);
				cs.setString(2,countyCode);
				return cs;
			}
		};
		CallableStatementCallback<List<ResultVO>> callback = new CallableStatementCallback<List<ResultVO>>() {
			@Override
			public List<ResultVO> doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				List<ResultVO> list = new ArrayList<ResultVO>();
				while (rs.next()) {
					ResultVO vo = new ResultVO();
					vo.setSerial(rs.getInt(Column.COLUMN_SERIAL.toString()));
					vo.setYear(rs.getString(Column.COLUMN_YEAR.toString()));
					vo.setContent(rs.getNString(Column.COLUMN_CONTENT.toString()));
					list.add(vo);
				}
				return list;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Boolean outputAdr270(final String year, final String countyCode, final JTextArea console) {
		console.append("開始產製戶籍明細資料...\n");
		final File file = new File(OUTPUT_PATH + "戶籍明細/" + year + "." + countyCode + "." + DateUtils.getCurrentDate() + ".L270.txt");
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_270.toString());
				cs.setString(1, year);
				cs.setString(2, countyCode);
				return cs;
			}
		};
		CallableStatementCallback<Boolean> callback = new CallableStatementCallback<Boolean>() {
			@Override
			public Boolean doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				Boolean result = outputResult(rs, file, console);
				return result;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Integer countAdr270(String year, String countyCode) {
		return jdbcTemplate.queryForObject(Sql.COUNT_ADR_270.toString(), Integer.class, year, countyCode);
	}

	@Override
	public List<ResultVO> getAdrPost(final String year, final String countyCode) {
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_POST.toString());
				cs.setString(1, year);
				cs.setString(2,countyCode);
				return cs;
			}
		};
		CallableStatementCallback<List<ResultVO>> callback = new CallableStatementCallback<List<ResultVO>>() {
			@Override
			public List<ResultVO> doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				List<ResultVO> list = new ArrayList<ResultVO>();
				while (rs.next()) {
					ResultVO vo = new ResultVO();
					vo.setSerial(rs.getInt(Column.COLUMN_SERIAL.toString()));
					vo.setYear(rs.getString(Column.COLUMN_YEAR.toString()));
					vo.setContent(rs.getNString(Column.COLUMN_CONTENT.toString()));
					list.add(vo);
				}
				return list;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Boolean outputAdrPost(final String year, final String countyCode, final JTextArea console) {
		console.append("開始產製POST資料...\n");
		final File file = new File(OUTPUT_PATH + "POST/" + year + "." + countyCode + "." + DateUtils.getCurrentDate() + ".POST.txt");
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_POST.toString());
				cs.setString(1, year);
				cs.setString(2, countyCode);
				return cs;
			}
		};
		CallableStatementCallback<Boolean> callback = new CallableStatementCallback<Boolean>() {
			@Override
			public Boolean doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				Boolean result = outputResult(rs, file, console);
				return result;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Integer countAdrPost(String year, String countyCode) {
		return jdbcTemplate.queryForObject(Sql.COUNT_ADR_POST.toString(), Integer.class, year, countyCode);
	}
	
	@Override
	public List<ResultVO> getAdrSprd(final String year) {
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_SPRD.toString());
				cs.setString(1, year);
				return cs;
			}
		};
		CallableStatementCallback<List<ResultVO>> callback = new CallableStatementCallback<List<ResultVO>>() {
			@Override
			public List<ResultVO> doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				List<ResultVO> list = new ArrayList<ResultVO>();
				while (rs.next()) {
					ResultVO vo = new ResultVO();
					vo.setSerial(rs.getInt(Column.COLUMN_SERIAL.toString()));
					vo.setYear(rs.getString(Column.COLUMN_YEAR.toString()));
					vo.setContent(rs.getNString(Column.COLUMN_CONTENT.toString()));
					list.add(vo);
				}
				return list;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Boolean outputAdrSprd(final String year, final JTextArea console) {
		console.append("開始產製特殊路街資料...\n");
		final File file = new File(OUTPUT_PATH + "特殊路街/" + year + "." + DateUtils.getCurrentDate() + ".SPRD.txt");
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_SPRD.toString());
				cs.setString(1, year);
				return cs;
			}
		};
		CallableStatementCallback<Boolean> callback = new CallableStatementCallback<Boolean>() {
			@Override
			public Boolean doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				Boolean result = outputResult(rs, file, console);
				return result;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Integer countAdrSprd(String year) {
		return jdbcTemplate.queryForObject(Sql.COUNT_ADR_SPRD.toString(), Integer.class, year);
	}

	@Override
	public Map<String, String> getCountyMap() {
		ResultSetExtractor<Map<String, String>> rse = new ResultSetExtractor<Map<String, String>>() {

			@Override
			public Map<String, String> extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				Map<String, String> map = new HashMap<String, String>();
				while (rs.next()) {
					String name = rs.getNString(Column.COLUMN_CTIY_NAME.toString());
					String code = rs.getNString(Column.COLUMN_CTIY_CODE.toString());
					map.put(name, code);
				}
				return map;
			}
			
		};
		return this.jdbcTemplate.query(Sql.SELECT_CTY_TAB.toString(), rse);
	}
	
	@Override
	public Boolean outputCity(final String year, final JTextArea console) {
		console.append("開始產製縣市代碼資料...\n");
		final File file = new File(OUTPUT_PATH + "縣市代碼/" + year + "." + DateUtils.getCurrentDate() + ".COUNTY.txt");
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_CITY.toString());
				cs.setString(1, year);
				return cs;
			}
		};
		CallableStatementCallback<Boolean> callback = new CallableStatementCallback<Boolean>() {
			@Override
			public Boolean doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				Boolean result = outputResult(rs, file, console);
				return result;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Boolean outputArea(final String year, final JTextArea console) {
		console.append("開始產製村里代碼資料...\n");
		final File file = new File(OUTPUT_PATH + "村里代碼/" + year + "." + DateUtils.getCurrentDate() + ".AREA.txt");
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_AREA.toString());
				cs.setString(1, year);
				return cs;
			}
		};
		CallableStatementCallback<Boolean> callback = new CallableStatementCallback<Boolean>() {
			@Override
			public Boolean doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				Boolean result = outputResult(rs, file, console);
				return result;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	private Boolean outputResult(final ResultSet rs, final File file, final JTextArea console) {
		console.append("正在輸出到檔案...\n");
		BufferedWriter output = null;
		file.getParentFile().mkdirs();
		Integer count = 0;
		final Integer outputSize = 10000;
		try {
			output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), ENCODING), BUFFER_SIZE);
			while (rs.next()) {
				String content = rs.getNString(Column.COLUMN_CONTENT.toString());
				if (content != null) {
					output.write(content);
					output.newLine();
					count++;
				}
				
				if (count % outputSize == 0) {
					console.append("已輸出" + count + "筆資料到檔案...\n");
				}
			}
			if (count % outputSize != 0) {
				console.append("已輸出" + count + "筆資料到檔案...\n");
			}
		} catch (Exception e) {
			return this.logError(e);
		} finally {
			try {
				if (output != null) {
					output.close();
				}
			} catch (Exception e) {
				return this.logError(e);
			}
		}
		return true;
	}
	
	private int[] batch200(final List<Adr200VO> batchList) {
		BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return batchList.size();
			}

			@Override
			public void setValues(PreparedStatement ps,
					int idx) throws SQLException {
				Adr200VO vo = batchList.get(idx);
				ps.setString(1, vo.getReservation());
				ps.setString(2, vo.getYear());
				ps.setString(3, vo.getCountyCode());
				ps.setString(4, vo.getHhCode());
				ps.setString(5, vo.getVil());
				ps.setString(6, vo.getNeig());
				ps.setString(7, vo.getAddress());
				ps.setString(8, vo.getEditDate());
				ps.setString(9, vo.getEditType());
			}
		};
		
		return jdbcTemplate.batchUpdate(Sql.INSERT_ADR_200.toString(), pss);
	}
	
	private int[] batch250(final List<Hud250VO> batchList) {
		BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return batchList.size();
			}

			@Override
			public void setValues(PreparedStatement ps,
					int idx) throws SQLException {
				Hud250VO vo = batchList.get(idx);
				ps.setString(1, vo.getSourceNo());
				ps.setString(2, vo.getYear());
				ps.setString(3, vo.getCountyCode());
				ps.setString(4, vo.getId());
				ps.setString(5, vo.getName());
				ps.setString(6, vo.getBirth());
				ps.setString(7, vo.getMarriage());
				ps.setString(8, vo.getRelMast());
				ps.setString(9, vo.getMastId());
				ps.setString(10, vo.getHudId());
				ps.setString(11, vo.getAborigine());
				ps.setString(12, vo.getRelMastCode());
				ps.setString(13, vo.getHhCode());
				ps.setString(14, vo.getMix1());
				ps.setString(15, vo.getAreaCode());
				ps.setString(16, vo.getCountyName());
				ps.setString(17, vo.getTownName());
				ps.setString(18, vo.getVil());
				ps.setString(19, vo.getNeig());
				ps.setString(20, vo.getAddress());
				ps.setString(21, vo.getMix2());
				ps.setString(22, vo.getMix3());
				ps.setString(23, vo.getMoveIn());
			}
		};
		return jdbcTemplate.batchUpdate(Sql.INSERT_HUD_250.toString(), pss);
	}

	@Override
	public Boolean calAdr380weight(String year, String countyCode) {
		if (jdbcTemplate.update(Sql.CALCULATE_ADR_380_WEIGHT.toString(), year, countyCode) < 0) {
			return false;
		}
		return true;
	}
	
	@Override
	public Boolean uploadAdrPost(String year, String countyCode, String url, final JTextArea console) {
		console.append("開始上傳POST資料...\n");
		File file = new File(url);
		File error = new File(ERROR_PATH + "POST/" + year + "." + DateUtils.getCurrentDate() + ".ERROR.POST.txt");
		BufferedReader input = null;
		BufferedWriter output = null;
		error.getParentFile().mkdirs();
		List<AdrPostVO> list = new ArrayList<AdrPostVO>();
		Map<String, String> map = this.getWordMap(countyCode);
		try {
			input = new BufferedReader(new InputStreamReader(new FileInputStream(file), ENCODING), BUFFER_SIZE);
			output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(error), ENCODING), BUFFER_SIZE);
			Integer serial = 0;
			Integer count = 0;
			
			jdbcTemplate.update(Sql.DISABLE_ADR_POST.toString());
			jdbcTemplate.update(Sql.DELETE_ADR_POST.toString(), year, countyCode);
			console.append("正在輸入到資料庫...\n");
			for (String line = null; (line = input.readLine()) != null; ) {
				serial++;
				byte[] bytes = line.getBytes(ENCODING);
				if (bytes.length == 84) {
//					String countyCode = new String(Arrays.copyOfRange(bytes, 80, 82), ENCODING);
					String areaTown = new String(Arrays.copyOfRange(bytes, 80, 84), ENCODING);
					String countyName = new String(Arrays.copyOfRange(bytes, 1, 7), ENCODING);
					String townName = new String(Arrays.copyOfRange(bytes, 7, 15), ENCODING);
					String str = new String(Arrays.copyOfRange(bytes, 15, 33), ENCODING);
					String sec = new String(Arrays.copyOfRange(bytes, 33, 45), ENCODING);
					String secC = new String(Arrays.copyOfRange(bytes, 45, 47), ENCODING);
					String lan = new String(Arrays.copyOfRange(bytes, 47, 63), ENCODING);
					String lanC = new String(Arrays.copyOfRange(bytes, 63, 65), ENCODING);
					String aly = new String(Arrays.copyOfRange(bytes, 65, 77), ENCODING);
					String alyC = new String(Arrays.copyOfRange(bytes, 77, 79), ENCODING);
					String adrAly = new String(Arrays.copyOfRange(bytes, 15, 79), ENCODING);
					
					AdrPostVO vo = new AdrPostVO();
					vo.setYear(year);
					vo.setCountyCode(StringUtils.removeSpace(countyCode));
					areaTown = StringUtils.removeSpace(areaTown);
					areaTown = this.replaceWord(areaTown, map);
					vo.setAreaTown(areaTown);
					countyName = StringUtils.removeSpace(countyName);
					countyName = this.replaceWord(countyName, map);
					vo.setCountyName(countyName);
					townName = StringUtils.removeSpace(townName);
					townName = this.replaceWord(townName, map);
					vo.setTownName(townName);
					str = StringUtils.removeSpace(str);
					str = this.replaceWord(str, map);
					vo.setStr(str);
					sec = StringUtils.removeSpace(sec);
					sec = this.replaceWord(sec, map);
					vo.setSec(StringUtils.removeSpace(sec));
					vo.setSecC(StringUtils.removeSpace(secC));
					lan = StringUtils.removeSpace(lan);
					lan = this.replaceWord(lan, map);
					vo.setLan(lan);
					vo.setLanC(StringUtils.removeSpace(lanC));
					aly = StringUtils.removeSpace(aly);
					aly = this.replaceWord(aly, map);
					vo.setAly(aly);
					vo.setAlyC(StringUtils.removeSpace(alyC));
					adrAly = StringUtils.removeSpace(adrAly);
					adrAly = this.replaceWord(adrAly, map);
					vo.setAdrAly(adrAly);
					
					list.add(vo);
					
					if (list.size() == BATCH_SIZE) {
						this.batchPost(list);
						count += list.size();
						console.append("已輸入" + count + "筆資料到資料庫...\n");
						list.clear();
					}
					
				} else {
					output.write("第" + serial +"筆資料長度: " + bytes.length);
					output.newLine();
					output.write(line);
					output.newLine();
				}
			}
			
			if (list.size() > 0) {
				this.batchPost(list);
				count += list.size();
				console.append("已輸入" + count + "筆資料到資料庫...\n");
			}
			jdbcTemplate.update(Sql.ENABLE_ADR_POST.toString());
		} catch (Exception e) {
			return this.logError(e);
		} finally {
			try {
				if (output != null) {
					output.close();
				}
				if (input != null) {
					input.close();
				}
			} catch (IOException e) {
				return this.logError(e);
			}
		}
		return true;
	}
	
	@Override
	public Boolean uploadAdrPost2(String year, String countyCode, String url, final JTextArea console) {
		console.append("開始上傳POST2資料...\n");
		File file = new File(url);
		File error = new File(ERROR_PATH + "POST2/" + year + "." + DateUtils.getCurrentDate() + ".ERROR.POST2.txt");
		BufferedReader input = null;
		BufferedWriter output = null;
		error.getParentFile().mkdirs();
		List<AdrPostVO> list = new ArrayList<AdrPostVO>();
		Map<String, String> map = this.getWordMap(countyCode);
		try {
			input = new BufferedReader(new InputStreamReader(new FileInputStream(file), ENCODING), BUFFER_SIZE);
			output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(error), ENCODING), BUFFER_SIZE);
			Integer serial = 0;
			Integer count = 0;
			
			jdbcTemplate.update(Sql.DISABLE_ADR_POST.toString());
			jdbcTemplate.update(Sql.DELETE_ADR_POST.toString(), year, countyCode);
			console.append("正在輸入到資料庫...\n");
			for (String line = null; (line = input.readLine()) != null; ) {
				serial++;
				byte[] bytes = line.getBytes(ENCODING);
				if (bytes.length == 105) {
//					String countyCode = new String(Arrays.copyOfRange(bytes, 98, 100), ENCODING);
					String areaTown = new String(Arrays.copyOfRange(bytes, 98, 102), ENCODING);
					String countyName = new String(Arrays.copyOfRange(bytes, 0, 6), ENCODING);
					String townName = new String(Arrays.copyOfRange(bytes, 6, 14), ENCODING);
					String str = new String(Arrays.copyOfRange(bytes, 23, 41), ENCODING);
					String sec = new String(Arrays.copyOfRange(bytes, 41, 53), ENCODING);
					String secC = new String(Arrays.copyOfRange(bytes, 53, 55), ENCODING);
					String lan = new String(Arrays.copyOfRange(bytes, 55, 71), ENCODING);
					String lanC = new String(Arrays.copyOfRange(bytes, 71, 73), ENCODING);
					String aly = new String(Arrays.copyOfRange(bytes, 73, 85), ENCODING);
					String alyC = new String(Arrays.copyOfRange(bytes, 85, 87), ENCODING);
					String adrAly = new String(Arrays.copyOfRange(bytes, 23, 87), ENCODING);
					
					AdrPostVO vo = new AdrPostVO();
					vo.setYear(year);
					vo.setCountyCode(StringUtils.removeSpace(countyCode));
					areaTown = StringUtils.removeSpace(areaTown);
					areaTown = this.replaceWord(areaTown, map);
					vo.setAreaTown(areaTown);
					countyName = StringUtils.removeSpace(countyName);
					countyName = this.replaceWord(countyName, map);
					vo.setCountyName(countyName);
					townName = StringUtils.removeSpace(townName);
					townName = this.replaceWord(townName, map);
					vo.setTownName(townName);
					str = StringUtils.removeSpace(str);
					str = this.replaceWord(str, map);
					vo.setStr(str);
					sec = StringUtils.removeSpace(sec);
					sec = this.replaceWord(sec, map);
					vo.setSec(StringUtils.removeSpace(sec));
					vo.setSecC(StringUtils.removeSpace(secC));
					lan = StringUtils.removeSpace(lan);
					lan = this.replaceWord(lan, map);
					vo.setLan(lan);
					vo.setLanC(StringUtils.removeSpace(lanC));
					aly = StringUtils.removeSpace(aly);
					aly = this.replaceWord(aly, map);
					vo.setAly(aly);
					vo.setAlyC(StringUtils.removeSpace(alyC));
					adrAly = StringUtils.removeSpace(adrAly);
					adrAly = this.replaceWord(adrAly, map);
					vo.setAdrAly(adrAly);
					
					list.add(vo);
					
					if (list.size() == BATCH_SIZE) {
						this.batchPost(list);
						count += list.size();
						console.append("已輸入" + count + "筆資料到資料庫...\n");
						list.clear();
					}
					
				} else {
					output.write("第" + serial +"筆資料長度: " + bytes.length);
					output.newLine();
					output.write(line);
					output.newLine();
				}
			}
			
			if (list.size() > 0) {
				this.batchPost(list);
				count += list.size();
				console.append("已輸入" + count + "筆資料到資料庫...\n");
			}
			jdbcTemplate.update(Sql.ENABLE_ADR_POST.toString());
		} catch (Exception e) {
			return this.logError(e);
		} finally {
			try {
				if (output != null) {
					output.close();
				}
				if (input != null) {
					input.close();
				}
			} catch (IOException e) {
				return this.logError(e);
			}
		}
		return true;
	}
	
	@Override
	public Boolean uploadAdrSprd(String countyCode, String url, final JTextArea console) {
		console.append("開始上船特殊路街資料...\n");
		File file = new File(url);
		File error = new File(ERROR_PATH + "特殊路街/" + DateUtils.getCurrentDate() + ".ERROR.SPRD.txt");
		BufferedReader input = null;
		BufferedWriter output = null;
		error.getParentFile().mkdirs();
		List<AdrSprdVO> list = new ArrayList<AdrSprdVO>();
		Map<String, String> map = this.getWordMap(countyCode);
		try {
			input = new BufferedReader(new InputStreamReader(new FileInputStream(file), ENCODING), BUFFER_SIZE);
			output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(error), ENCODING), BUFFER_SIZE);
			Integer serial = 0;
			Integer count = 0;
			
			jdbcTemplate.update(Sql.DELETE_ADR_SPRD.toString(), countyCode);
			console.append("正在輸入到資料庫...\n");
			for (String line = null; (line = input.readLine()) != null; ) {
				serial++;
				byte[] bytes = line.getBytes(ENCODING);
				if (bytes.length == 70) {
//					String countyCode = new String(Arrays.copyOfRange(bytes, 59, 61), ENCODING);
					String countyName = new String(Arrays.copyOfRange(bytes, 1, 7), ENCODING);
					String townCode = new String(Arrays.copyOfRange(bytes, 61, 63), ENCODING);
					String townName = new String(Arrays.copyOfRange(bytes, 7, 15), ENCODING);
					String str = new String(Arrays.copyOfRange(bytes, 15, 33), ENCODING);
					
					AdrSprdVO vo = new AdrSprdVO();
					vo.setCountyCode(StringUtils.removeSpace(countyCode));
					countyName = StringUtils.removeSpace(countyName);
					countyName = this.replaceWord(countyName, map);
					vo.setCountyName(countyName);
					vo.setTownCode(StringUtils.removeSpace(townCode));
					townName = StringUtils.removeSpace(townName);
					townName = this.replaceWord(townName, map);
					vo.setTownName(townName);
					str = StringUtils.removeSpace(str);
					str = this.replaceWord(str, map);
					vo.setStr(str);
					
					if (vo.getStr() != null) {
						list.add(vo);
					}
					
					if (list.size() == BATCH_SIZE) {
						this.batchSprd(list);
						count += list.size();
						console.append("已輸入" + count + "筆資料到資料庫...\n");
						list.clear();
					}
					
				} else {
					output.write("第" + serial +"筆資料長度: " + bytes.length);
					output.newLine();
					output.write(line);
					output.newLine();
				}
			}
			
			if (list.size() > 0) {
				this.batchSprd(list);
				count += list.size();
				console.append("已輸入" + count + "筆資料到資料庫...\n");
			}
		} catch (Exception e) {
			return this.logError(e);
		} finally {
			try {
				if (output != null) {
					output.close();
				}
				if (input != null) {
					input.close();
				}
			} catch (IOException e) {
				return this.logError(e);
			}
		}
		return true;
	}

	private int[] batchPost(final List<AdrPostVO> batchList) {
		BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return batchList.size();
			}

			@Override
			public void setValues(PreparedStatement ps,
					int idx) throws SQLException {
				AdrPostVO vo = batchList.get(idx);
				ps.setString(1, vo.getYear());
				ps.setString(2, vo.getCountyCode());
				ps.setString(3, vo.getAreaTown());
				ps.setString(4, vo.getCountyName());
				ps.setString(5, vo.getTownName());
				ps.setString(6, vo.getStr());
				ps.setString(7, vo.getSec());
				ps.setString(8, vo.getSecC());
				ps.setString(9, vo.getLan());
				ps.setString(10, vo.getLanC());
				ps.setString(11, vo.getAly());
				ps.setString(12, vo.getAlyC());
				ps.setString(13, vo.getAdrAly());
			}
		};
		return jdbcTemplate.batchUpdate(Sql.INSERT_ADR_POST.toString(), pss);
	}
	
	@Override
	public Boolean uploadAdrArea(String countyCode, String url, final JTextArea console) {
		console.append("開始上傳村里代碼資料...\n");
		File file = new File(url);
		File error = new File(ERROR_PATH + "村里代碼/" + DateUtils.getCurrentDate() + ".ERROR.AREA.txt");
		BufferedReader input = null;
		BufferedWriter output = null;
		error.getParentFile().mkdirs();
		List<AdrAreaVO> list = new ArrayList<AdrAreaVO>();
		Map<String, String> map = this.getWordMap(countyCode);
		try {
			input = new BufferedReader(new InputStreamReader(new FileInputStream(file), ENCODING), BUFFER_SIZE);
			output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(error), ENCODING), BUFFER_SIZE);
			Integer serial = 0;
			Integer count = 0;
			
			jdbcTemplate.update(Sql.DISABLE_ADR_AREA.toString());
			jdbcTemplate.update(Sql.DELETE_ADR_AREA.toString(), countyCode);
			console.append("正在輸入到資料庫...\n");
			for (String line = null; (line = input.readLine()) != null; ) {
				serial++;
				byte[] bytes = line.getBytes(ENCODING);
				if (bytes.length == 31) {
//					String countyCode = new String(Arrays.copyOfRange(bytes, 0, 2), ENCODING);
					String countyName = new String(Arrays.copyOfRange(bytes, 8, 14), ENCODING);
					String townCode = new String(Arrays.copyOfRange(bytes, 2, 4), ENCODING);
					String townName = new String(Arrays.copyOfRange(bytes, 14, 22), ENCODING);
					String vilCode = new String(Arrays.copyOfRange(bytes, 4, 7), ENCODING);
					String vilName = new String(Arrays.copyOfRange(bytes, 22, 30), ENCODING);
					
					AdrAreaVO vo = new AdrAreaVO();
					vo.setCountyCode(StringUtils.removeSpace(countyCode));
					countyName = StringUtils.removeSpace(countyName);
					countyName = this.replaceWord(countyName, map);
					vo.setCountyName(countyName);
					vo.setTownCode(StringUtils.removeSpace(townCode));
					townName = StringUtils.removeSpace(townName);
					townName = this.replaceWord(townName, map);
					vo.setTownName(townName);
					vo.setVilCode(StringUtils.removeSpace(vilCode));
					vilName = StringUtils.removeSpace(vilName);
					vilName = this.replaceWord(vilName, map);
					vo.setVilName(vilName);
					
					list.add(vo);
					
					if (list.size() == BATCH_SIZE) {
						this.batchArea(list);
						count += list.size();
						console.append("已輸入" + count + "筆資料到資料庫...\n");
						list.clear();
					}
					
				} else {
					output.write("第" + serial +"筆資料長度: " + bytes.length);
					output.newLine();
					output.write(line);
					output.newLine();
				}
			}
			
			if (list.size() > 0) {
				this.batchArea(list);
				count += list.size();
				console.append("已輸入" + count + "筆資料到資料庫...\n");
			}
			jdbcTemplate.update(Sql.ENABLE_ADR_AREA.toString());
		} catch (Exception e) {
			return this.logError(e);
		} finally {
			try {
				if (output != null) {
					output.close();
				}
				if (input != null) {
					input.close();
				}
			} catch (IOException e) {
				return this.logError(e);
			}
		}
		return true;
	}
	
	private int[] batchArea(final List<AdrAreaVO> batchList) {
		BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return batchList.size();
			}

			@Override
			public void setValues(PreparedStatement ps,
					int idx) throws SQLException {
				AdrAreaVO vo = batchList.get(idx);
				ps.setString(1, vo.getCountyCode());
				ps.setString(2, vo.getCountyName());
				ps.setString(3, vo.getTownCode());
				ps.setString(4, vo.getTownName());
				ps.setString(5, vo.getVilCode());
				ps.setString(6, vo.getVilName());
			}
		};
		return jdbcTemplate.batchUpdate(Sql.INSERT_ADR_AREA.toString(), pss);
	}
	
	private int[] batchSprd(final List<AdrSprdVO> batchList) {
		BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return batchList.size();
			}

			@Override
			public void setValues(PreparedStatement ps,
					int idx) throws SQLException {
				AdrSprdVO vo = batchList.get(idx);
				ps.setString(1, vo.getCountyCode());
				ps.setString(2, vo.getCountyName());
				ps.setString(3, vo.getTownCode());
				ps.setString(4, vo.getTownName());
				ps.setString(5, vo.getStr());
			}
		};
		return jdbcTemplate.batchUpdate(Sql.INSERT_ADR_SPRD.toString(), pss);
	}

	@Override
	public Boolean disableAdr380() {
		jdbcTemplate.update(Sql.DISABLE_ADR_380.toString());
		return true;
	}

	@Override
	public Boolean enableAdr380() {
		jdbcTemplate.update(Sql.ENABLE_ADR_380.toString());
		return true;
	}

	@Override
	public Map<String, String> getWordMap(final String countyCode) {
		PreparedStatementCreator psc = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement stmt = conn.prepareStatement(Sql.SELECT_WORD_MAP.toString());
				stmt.setString(1, countyCode);
				return stmt;
			}
		};
		
		ResultSetExtractor<Map<String, String>> rse = new ResultSetExtractor<Map<String, String>>() {
			@Override
			public Map<String, String> extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				Map<String, String> map = new LinkedHashMap<String, String>();
				while (rs.next()) {
					String key = rs.getNString("wrong_word");
					String value = rs.getNString("right_word");
					map.put(key, value);
				}
				return map;
			}
		};
		
		return jdbcTemplate.query(psc, rse);
	}
	
	private String getRegex(Map<String, String> map) {
		StringBuilder sb = new StringBuilder();
		sb.append(".*[");
		for (String key : map.keySet()) {
			sb.append(key);
		}
		sb.append("].*");
		return sb.toString();
	}
	
	private String replaceWord(String str, Map<String, String> map) {
		String regex = getRegex(map);
		if (str != null && !regex.equals(".*[].*")) {
			if (str.matches(regex)) {
				Set<Entry<String, String>> set = map.entrySet();
				for (Entry<String, String> entry : set) {
					String key = entry.getKey();
					String value = entry.getValue();
					str = str.replaceAll(key, value);
				}
			}
		}
		return str;
	}

	@Override
	public Boolean deleteAdr380(String year, String countyCode) {
		jdbcTemplate.update(Sql.DELETE_ADR_380.toString(), year, countyCode);
		return true;
	}

	@Override
	public Boolean initAdr220(String year, String countyCode) {
		jdbcTemplate.update(Sql.PROCEDURE_INIT_ADR_220.toString(), year, countyCode);
		return true;
	}

	@Override
	public Boolean createAdr220(String year, String countyCode) {
		jdbcTemplate.update(Sql.PROCEDURE_CREATE_ADR_220.toString(), year, countyCode);
		return true;
	}

	@Override
	public List<AdrStrBlockOrderVO> order150(final String year, final String countyCode) {
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ORDER_150.toString());
				cs.setString(1, year);
				cs.setString(2, countyCode);
				return cs;
			}
		};
		CallableStatementCallback<List<AdrStrBlockOrderVO>> callback = new CallableStatementCallback<List<AdrStrBlockOrderVO>>() {
			@Override
			public List<AdrStrBlockOrderVO> doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				List<AdrStrBlockOrderVO> list = new LinkedList<AdrStrBlockOrderVO>();
				ResultSet rs = cs.getResultSet();
				while (rs.next()) {
					AdrStrBlockOrderVO vo = new AdrStrBlockOrderVO();
					String year = rs.getNString("year");
					String countyCode = rs.getNString("county_code");
					String areaCode = rs.getNString("area_code");
					String countyName = rs.getNString("county_name");
					String townName = rs.getNString("town_name");
					String vil = rs.getNString("vil");
					String str = rs.getNString("str");
					String sec = rs.getNString("sec");
					String secC = rs.getNString("sec_c");
					String lan = rs.getNString("lan");
					String lanC = rs.getNString("lan_c");
					String aly = rs.getNString("aly");
					String alyC = rs.getNString("aly_c");
					String no1 = rs.getNString("no1");
					String oddEven = rs.getNString("odd_even");
					
					vo.setYear(year);
					vo.setCountyCode(countyCode);
					vo.setAreaCode(areaCode);
					vo.setCountyName(countyName);
					vo.setTownName(townName);
					vo.setVil(vil);
					vo.setStr(str);
					vo.setSec(sec);
					vo.setSecC(secC);
					vo.setLan(lan);
					vo.setLanC(lanC);
					vo.setAly(aly);
					vo.setAlyC(alyC);
					vo.setNo1(no1);
					vo.setOddEven(oddEven);
					
					list.add(vo);
				}
				return list;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}

	@Override
	public List<AdrStrBlockOrderVO> order168(final String year, final String countyCode) {
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ORDER_168.toString());
				cs.setNString(1, year);
				cs.setNString(2, countyCode);
				return cs;
			}
		};
		CallableStatementCallback<List<AdrStrBlockOrderVO>> callback = new CallableStatementCallback<List<AdrStrBlockOrderVO>>() {
			@Override
			public List<AdrStrBlockOrderVO> doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				List<AdrStrBlockOrderVO> list = new LinkedList<AdrStrBlockOrderVO>();
				ResultSet rs = cs.getResultSet();
				while (rs.next()) {
					AdrStrBlockOrderVO vo = new AdrStrBlockOrderVO();
					String year = rs.getNString("year");
					String countyCode = rs.getNString("county_code");
					String areaCode = rs.getNString("area_code");
					String countyName = rs.getNString("county_name");
					String townName = rs.getNString("town_name");
					String vil = rs.getNString("vil");
					String str = rs.getNString("str");
					String sec = rs.getNString("sec");
					String secC = rs.getNString("sec_c");
					String lan = rs.getNString("lan");
					String lanC = rs.getNString("lan_c");
					String aly = rs.getNString("aly");
					String alyC = rs.getNString("aly_c");
					String subAly = rs.getNString("sub_aly");
					String subAlyC = rs.getNString("sub_aly_c");
					String no1 = rs.getNString("no1");
					String oddEven = rs.getNString("odd_even");
					
					vo.setYear(year);
					vo.setCountyCode(countyCode);
					vo.setAreaCode(areaCode);
					vo.setCountyName(countyName);
					vo.setTownName(townName);
					vo.setVil(vil);
					vo.setStr(str);
					vo.setSec(sec);
					vo.setSecC(secC);
					vo.setLan(lan);
					vo.setLanC(lanC);
					vo.setAly(aly);
					vo.setAlyC(alyC);
					vo.setSubAly(subAly);
					vo.setSubAlyC(subAlyC);
					vo.setNo1(no1);
					vo.setOddEven(oddEven);
					
					list.add(vo);
				}
				return list;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}

	@Override
	public Boolean insert150(String year, String countyCode, List<AdrStrBlockVO> list, JTextArea console) {
		jdbcTemplate.update(Sql.DELETE_150.toString(), year, countyCode);
		List<AdrStrBlockVO> batchList = new ArrayList<AdrStrBlockVO>();
		int total = list.size() / BATCH_SIZE;
		int count = 0;
		for (int idx = 0; idx < total; idx++) {
			batchList.addAll(list.subList(idx * BATCH_SIZE, (idx + 1) * BATCH_SIZE));
			this.batch150(batchList);
			count += batchList.size();
			batchList.clear();
			console.append("已輸入" + count + "筆資料到資料庫...\n");
		}
		batchList.addAll(list.subList(count, list.size()));
		this.batch150(batchList);
		count += batchList.size();
		console.append("已輸入" + count + "筆資料到資料庫...\n");
		return true;
	}

	@Override
	public Boolean insert168(String year, String countyCode, List<AdrStrBlockVO> list, JTextArea console) {
		jdbcTemplate.update(Sql.DELETE_168.toString(), year, countyCode);
		List<AdrStrBlockVO> batchList = new ArrayList<AdrStrBlockVO>();
		int total = list.size() / BATCH_SIZE;
		int count = 0;
		for (int idx = 0; idx < total; idx++) {
			batchList.addAll(list.subList(idx * BATCH_SIZE, (idx + 1) * BATCH_SIZE));
			this.batch168(batchList);
			count += batchList.size();
			batchList.clear();
			console.append("已輸入" + count + "筆資料到資料庫...\n");
		}
		batchList.addAll(list.subList(count, list.size()));
		this.batch168(batchList);
		count += batchList.size();
		console.append("已輸入" + count + "筆資料到資料庫...\n");
		return true;
	}

	private int[] batch150(final List<AdrStrBlockVO> batchList) {
		BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return batchList.size();
			}

			@Override
			public void setValues(PreparedStatement ps,
					int idx) throws SQLException {
				//[year], county_code, area_code, census_code, county_name, town_name, vil, [str], sec, sec_c, lan, lan_c, aly, aly_c, bgn_no, end_no, odd_even, block_no
				AdrStrBlockVO vo = batchList.get(idx);
				ps.setNString(1, vo.getYear());
				ps.setNString(2, vo.getCountyCode());
				ps.setNString(3, vo.getAreaCode());
				ps.setNString(4, "***");
				ps.setNString(5, vo.getCountyName());
				ps.setNString(6, vo.getTownName());
				ps.setNString(7, vo.getVil());
				ps.setNString(8, vo.getStr());
				ps.setNString(9, vo.getSec());
				ps.setNString(10, vo.getSecC());
				ps.setNString(11, vo.getLan());
				ps.setNString(12, vo.getLanC());
				ps.setNString(13, vo.getAly());
				ps.setNString(14, vo.getAlyC());
				ps.setNString(15, vo.getBgnNo());
				ps.setNString(16, vo.getEndNo());
				ps.setNString(17, vo.getOddEven());
				ps.setNString(18, vo.getBlockNo());
			}
		};
		return jdbcTemplate.batchUpdate(Sql.INSERT_150.toString(), pss);
	}
	
	private int[] batch168(final List<AdrStrBlockVO> batchList) {
		BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return batchList.size();
			}

			@Override
			public void setValues(PreparedStatement ps,
					int idx) throws SQLException {
				AdrStrBlockVO vo = batchList.get(idx);
				ps.setNString(1, vo.getYear());
				ps.setNString(2, vo.getCountyCode());
				ps.setNString(3, vo.getAreaCode());
				ps.setNString(4, "***");
				ps.setNString(5, vo.getCountyName());
				ps.setNString(6, vo.getTownName());
				ps.setNString(7, vo.getVil());
				ps.setNString(8, vo.getStr());
				ps.setNString(9, vo.getSec());
				ps.setNString(10, vo.getSecC());
				ps.setNString(11, vo.getLan());
				ps.setNString(12, vo.getLanC());
				ps.setNString(13, vo.getAly());
				ps.setNString(14, vo.getAlyC());
				ps.setNString(15, vo.getSubAly());
				ps.setNString(16, vo.getSubAlyC());
				ps.setNString(17, vo.getBgnNo());
				ps.setNString(18, vo.getEndNo());
				ps.setNString(19, vo.getOddEven());
				ps.setNString(20, vo.getBlockNo());
			}
		};
		return jdbcTemplate.batchUpdate(Sql.INSERT_168.toString(), pss);
	}

	@Override
	public Boolean outputAdr380(final String year, final String countyCode, final JTextArea console) {
		console.append("開始產製地址分欄資料...\n");
		final File file = new File(OUTPUT_PATH + "地址分欄/" + year + "." + countyCode + "." + DateUtils.getCurrentDate() + ".L398.txt");
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_380.toString());
				cs.setString(1, year);
				cs.setString(2, countyCode);
				return cs;
			}
		};
		CallableStatementCallback<Boolean> callback = new CallableStatementCallback<Boolean>() {
			@Override
			public Boolean doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				Boolean result = outputResult(rs, file, console);
				return result;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Boolean outputAdr150(final String year, final String countyCode, final JTextArea console) {
		console.append("開始產製鄰街廓資料...\n");
		final File file = new File(OUTPUT_PATH + "鄰街廓/" + year + "." + countyCode + "." + DateUtils.getCurrentDate() + ".L150.txt");
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_150.toString());
				cs.setString(1, year);
				cs.setString(2, countyCode);
				return cs;
			}
		};
		CallableStatementCallback<Boolean> callback = new CallableStatementCallback<Boolean>() {
			@Override
			public Boolean doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				Boolean result = outputResult(rs, file, console);
				return result;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}
	
	@Override
	public Boolean outputAdr168(final String year, final String countyCode, final JTextArea console) {
		console.append("開始產製村里街廓資料...\n");
		final File file = new File(OUTPUT_PATH + "村里街廓/" + year + "." + countyCode + "." + DateUtils.getCurrentDate() + ".L168.txt");
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PROCEDURE_ADR_168.toString());
				cs.setString(1, year);
				cs.setString(2, countyCode);
				return cs;
			}
		};
		CallableStatementCallback<Boolean> callback = new CallableStatementCallback<Boolean>() {
			@Override
			public Boolean doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				Boolean result = outputResult(rs, file, console);
				return result;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}

	@Override
	public Boolean outputWordMap(String year, final JTextArea console) {
		console.append("開始產製異陳字資料...\n");
		final File file = new File(OUTPUT_PATH + "異陳字/" + year + "." + DateUtils.getCurrentDate() + ".WORDMAP.txt");
		CallableStatementCreator creator = new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall(Sql.PRODECURE_WORD_MAP.toString());
				return cs;
			}
		};
		CallableStatementCallback<Boolean> callback = new CallableStatementCallback<Boolean>() {
			@Override
			public Boolean doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				ResultSet rs = cs.getResultSet();
				Boolean result = outputResult(rs, file, console);
				return result;
			}
		};
		return jdbcTemplate.execute(creator, callback);
	}

	@Override
	public Boolean uploadWordMap(String url, JTextArea console) {
		console.append("開始上傳異陳字資料...\n");
		File file = new File(url);
		File error = new File(ERROR_PATH + "異陳字/" + DateUtils.getCurrentDate() + ".ERROR.WORDMAP.txt");
		BufferedReader input = null;
		BufferedWriter output = null;
		error.getParentFile().mkdirs();
		List<WordMapVO> list = new ArrayList<WordMapVO>();
		try {
			input = new BufferedReader(new InputStreamReader(new FileInputStream(file), ENCODING), BUFFER_SIZE);
			output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(error), ENCODING), BUFFER_SIZE);
			Integer serial = 0;
			Integer count = 0;
			
			jdbcTemplate.update(Sql.TRUNCATE_WORD_MAP.toString());
			console.append("正在輸入到資料庫...\n");
			for (String line = null; (line = input.readLine()) != null; ) {
				serial++;
				byte[] bytes = line.getBytes(ENCODING);
				if (bytes.length == 50) {
					String countyCode = new String(Arrays.copyOfRange(bytes, 48, 50), ENCODING);
					String countyName = new String(Arrays.copyOfRange(bytes, 0, 6), ENCODING);
					String wrongWord = new String(Arrays.copyOfRange(bytes, 7, 27), ENCODING);
					String rightWord = new String(Arrays.copyOfRange(bytes, 27, 47), ENCODING);
					
					WordMapVO vo = new WordMapVO();
					vo.setCountyCode(StringUtils.removeSpace(countyCode));
					vo.setCountyName(StringUtils.removeSpace(countyName));
					vo.setWrongWord(StringUtils.removeSpace(wrongWord));
					vo.setRightWord(StringUtils.removeSpace(rightWord));
					
					list.add(vo);
					
					if (list.size() == BATCH_SIZE) {
						this.batchWordMap(list);
						count += list.size();
						console.append("已輸入" + count + "筆資料到資料庫...\n");
						list.clear();
					}
					
				} else {
					output.write("第" + serial +"筆資料長度: " + bytes.length);
					output.newLine();
					output.write(line);
					output.newLine();
				}
			}
			
			if (list.size() > 0) {
				this.batchWordMap(list);
				count += list.size();
				console.append("已輸入" + count + "筆資料到資料庫...\n");
			}
		} catch (Exception e) {
			return this.logError(e);
		} finally {
			try {
				if (output != null) {
					output.close();
				}
				if (input != null) {
					input.close();
				}
			} catch (IOException e) {
				return this.logError(e);
			}
		}
		return true;
	}
	
	private int[] batchWordMap(final List<WordMapVO> batchList) {
		BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return batchList.size();
			}

			@Override
			public void setValues(PreparedStatement ps,
					int idx) throws SQLException {
				WordMapVO vo = batchList.get(idx);
				ps.setNString(1, vo.getCountyCode());
				ps.setNString(2, vo.getCountyName());
				ps.setNString(3, vo.getWrongWord());
				ps.setNString(4, vo.getRightWord());
			}
		};
		return jdbcTemplate.batchUpdate(Sql.INSERT_WORD_MAP.toString(), pss);
	}
}
